package com.smart5G.dao;

import com.smart5G.model.Order;
import com.smart5G.model.OrderExample;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

@Mapper
@Component
public interface OrderMapper {
    int countByExample(OrderExample example);

    int deleteByExample(OrderExample example);

    int deleteByPrimaryKey(String orderId);

    int insert(Order record);

    int insertSelective(Order record);

    List<Order> selectByExample(OrderExample example);

    Order selectByPrimaryKey(String orderId);

    int updateByExampleSelective(@Param("record") Order record, @Param("example") OrderExample example);

    int updateByExample(@Param("record") Order record, @Param("example") OrderExample example);

    int updateByPrimaryKeySelective(Order record);

    int updateByPrimaryKey(Order record);

//    @Select("select sum(price) from `order` where province=#{province}")
    BigDecimal getAllSaleroom(String province);

    @Select("SELECT device_brand as brand,count(*) as quantity FROM `order` WHERE province = #{province} GROUP BY device_brand")
    ArrayList<Map<String,Integer>> getBrandQuantity(String province);

    @Select("SELECT DATE_FORMAT( create_time, '%Y%m%d' ) days, sum( price ) count FROM `order`  where service_id=#{serviceId} GROUP BY days ORDER BY days DESC LIMIT 0,7;")
    ArrayList<Map<String,Object>> getOrderMashupByDay(String serviceId);

    @Select("SELECT DATE_FORMAT( create_time, '%Y%u' ) weeks, sum( price ) count FROM `order`  where province='山东省' and service_id=#{serviceId} GROUP BY weeks ORDER BY weeks DESC LIMIT 0,7;")
    ArrayList<Map<String,Object>> getOrderMashupByWeek(String serviceId);

    @Select("SELECT DATE_FORMAT( create_time, '%Y%m' ) months, sum( price ) count FROM `order`  where service_id=#{serviceId} GROUP BY months ORDER BY months DESC LIMIT 0,7;")
    ArrayList<Map<String,Object>> getOrderMashupByMonth(String serviceId);
    @Select("select count(*) from `order` where province=#{province} and sex=0;")
    int getManCount(String province);
    @Select("select count(*) from `order` where province=#{province} and sex=1;")
    int getWomanCount(String province);
    @Select("select count(*) from `order` where province=#{province} and sex=0 and age <=20;")
    int getMan1(String province);
    @Select("select count(*) from `order` where province=#{province} and sex=1 and age <=20;")
    int getWoman1(String province);
    @Select("select count(*) from `order` where province=#{province} and sex=0 and age>20 and age <=30;")
    int getMan2(String province);
    @Select("select count(*) from `order` where province=#{province} and sex=1 and age>20 and age <=30;")
    int getWoman2(String province);
    @Select("select count(*) from `order` where province=#{province} and sex=0 and age>30 and age <=40;")
    int getMan3(String province);
    @Select("select count(*) from `order` where province=#{province} and sex=1 and age>30 and age <=40;")
    int getWoman3(String province);
    @Select("select count(*) from `order` where province=#{province} and sex=0 and age>40 and age <=50;")
    int getMan4(String province);
    @Select("select count(*) from `order` where province=#{province} and sex=1 and age>40 and age <=50;")
    int getWoman4(String province);
    @Select("select count(*) from `order` where province=#{province} and sex=0 and age >50;")
    int getMan5(String province);
    @Select("select count(*) from `order` where province=#{province} and sex=1 and age >50;")
    int getWoman5(String province);
    @Select("select province_name as name,count1 as value from province_info ;")
    List<Map<String,String>> getAllProvinceData();
}